
drop table jms_dm.dm_sham_send_monitor_sum_dt
;
CREATE TABLE jms_dm.dm_sham_send_monitor_sum_dt
(
network_code varchar(200) comment '网点code(责任)',
network_type varchar(200) comment '网点类型1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点',
agent_code varchar(200) comment '代理区code(责任)',
taking_time date comment '揽收时间',
network_name varchar(200) comment '网点名称(责任)',
agent_name varchar(200) comment '代理区名称(责任)',
total_cnt int comment '总件量',
start_code_stay_ovtime_cnt int comment '始发网点停留超时票件量',
start_code_center_ovtime_cnt int comment '始发网点-始发中心超时票件量',
first_center_ovtime_cnt int comment '始发中心停留超时超时票件量',
first_center_transit_ovtime_cnt int comment '始发中心-中转机构票件量',
transit_center_stay_ovtime_cnt int comment '中转机构停留超时票件量',
transit_center_run_ovtime_cnt int comment '中转环节流转超时票件量',
end_center_cnt int comment '末端中心票件量',
end_center_ovtime_cnt int comment '末端中心流转超时票件量',
end_code_stay_ovtime_cnt int comment '末端网点停留超时票件量'
) ENGINE=OLAP
DUPLICATE KEY(`network_code`,`network_type`, `agent_code`, `taking_time`)
COMMENT "虚假发货监控-汇总表"
PARTITION BY RANGE(`taking_time`)
(START ('2023-08-01') END ('2023-09-30') EVERY (INTERVAL 1 day) )
DISTRIBUTED BY HASH(`network_code`) BUCKETS 4
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-366",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "4",
"in_memory" = "false",
"storage_format" = "V2"
);